package com.self.datebasecompare.test;

import com.self.datebasecompare.config.DatabaseTypeComparator;
import org.springframework.stereotype.Component;
import org.yaml.snakeyaml.Yaml;

import java.io.InputStream;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author jhj
 */
@Component
public class StartupTask {

    // 可配置项
    private static final int PAGE_SIZE = 400; // 分页大小
    private static final int MAX_MATCH_LENGTH = 300; // 最多比较字符长度
    // 以下配置自动获取
    private static Connection mysqlConnection = null;
    private static Connection oracleConnection = null;
    private static String secondaryUser = null;
    private static Long maxCount = 0L; // 存储两表间最大的行数
    private static final DateTimeFormatter MYSQL_DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter ORACLE_DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S"); // oracle读取的日期类型会自动增加毫秒.0

    public static void main(String[] args) throws SQLException {
        try {
            HashMap<String, Connection> connectionMap = StartupTask.getConnection();
            mysqlConnection = connectionMap.get("mysqlConnection");
            oracleConnection = connectionMap.get("oracleConnection");

            DatabaseMetaData mysqlMetaData = mysqlConnection.getMetaData();
            DatabaseMetaData oracleMetaData = oracleConnection.getMetaData();

            // 获取两库相同表名的表
            Set<String> commonTable = getCommonTable(mysqlMetaData, oracleMetaData);
            if (commonTable.isEmpty()) {
                System.out.println("不存在相同表！");
                return;
            }
            // 将相同表作为循环，对比两库中相同表的字段类型、约束性、数据条数、数据是否一致
            for (String tableName : commonTable) {
                System.out.println("正在对比表：" + tableName);
                // 用一个表测试，后续删除
                if ("hy_rectification".equals(tableName)){
                // 比较约束性
                compareConstraints(mysqlConnection, oracleConnection, tableName);
                // 比较字段类型
                compareColumnTypes(mysqlConnection, oracleConnection, tableName);
                // 比较数据条数是否一致，返回其中最大值用于分页
                maxCount = compareRowCount(mysqlConnection, oracleConnection, tableName);
                System.out.println("最大数据条数：" + maxCount);
                // 比较数据一致性，数据量过大采用分页形式比较，数据无需对齐，采用回填数据的方法，结果返回不一致数据的id字段值
                compareDataConsistency(mysqlConnection, oracleConnection, tableName, maxCount);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            oracleConnection.close();
            mysqlConnection.close();
        }
    }

    /**
     * 比较两表约束性信息
     * @param mysqlConnection
     * @param oracleConnection
     * @param tableName
     */
    private static void compareConstraints(Connection mysqlConnection, Connection oracleConnection, String tableName) {
        try {
            // 获取MySQL表的约束信息
            Map<String, String> mysqlConstraints = getMysqlConstraints(mysqlConnection, tableName);
            System.out.println("mysql约束信息:" + mysqlConstraints);
            // 获取Oracle表的约束信息
            Map<String, String> oracleConstraints = getOracleConstraints(oracleConnection, tableName);
            System.out.println("oracle约束信息:" + oracleConstraints);
            // 比较两个Map是否完全相同
            boolean areEqual = mysqlConstraints.equals(oracleConstraints);
            if (areEqual) {
                System.out.println("表 " + tableName + " 约束性信息相同.");
            } else {
                System.out.println("表 " + tableName + " 约束性信息不同.");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 从给定的ORACLE数据库连接中获取指定表的主键、外键和唯一性索引约束信息及其列名。
     *
     * @param connection 数据库连接
     * @param tableName 表名
     * @return 包含约束名称、类型和列名的Map，其中键是约束名+列名列表
     * @throws Exception 如果发生错误
     */
    private static HashMap<String, String> getOracleConstraints(Connection connection, String tableName) throws SQLException {
        HashMap<String, String> constraints = new HashMap<>();
        String quoteChar = "'";
        String query = "SELECT " +
                "    ucc.COLUMN_NAME," +
                "    uc.CONSTRAINT_TYPE " +
                "FROM " +
                "    USER_CONSTRAINTS uc " +
                "JOIN " +
                "    USER_CONS_COLUMNS ucc ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME " +
                "WHERE " +
                "    uc.TABLE_NAME =  " +
                quoteChar + tableName + quoteChar +
                "AND uc.OWNER = " +
                quoteChar + secondaryUser.toUpperCase(Locale.ROOT) + quoteChar +
                "AND uc.CONSTRAINT_TYPE IN ('P', 'R', 'U')";
        String sql = "select * from "
                +"(select t.*,rownum num from "
                + quoteChar + tableName + quoteChar
                + "t where rownum<=? ) "
                +"where num>?";

        String columnType = "";
        String columnName = "";

        try (PreparedStatement statement = connection.prepareStatement(query)) {
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    columnType = rs.getString("CONSTRAINT_TYPE");
                    // 映射为mysql的约束类型
                    if ("P".equals(columnType)) {
                        columnType = "PRIMARY KEY";
                    } else if ("R".equals(columnType)) {
                        columnType = "FOREIGN KEY";
                    } else if ("U".equals(columnType)) {
                        columnType = "UNIQUE";
                    }
                    columnName = columnName + "|" + rs.getString("COLUMN_NAME");
                    constraints.put(columnType, columnName);
                }
            }
        }
        return constraints;
    }

    /**
     * 从给定的MYSQL数据库连接中获取指定表的主键、外键和唯一性索引约束信息及其列名。
     *
     * @param connection 数据库连接
     * @param tableName 表名
     * @return 包含约束名称、类型和列名的Map，其中键是约束名+列名列表
     * @throws Exception 如果发生错误
     */
    private static HashMap<String, String> getMysqlConstraints(Connection connection, String tableName) throws SQLException {
        HashMap<String, String> constraints = new HashMap<>();
        String query = "SELECT DISTINCT " +
                "    tc.table_name, " +
                "    c.column_name, " +
                "    tc.constraint_name, " +
                "    tc.constraint_type " +
                "FROM " +
                "    information_schema.table_constraints AS tc " +
                "JOIN " +
                "    information_schema.key_column_usage AS kcu " +
                "ON " +
                "    tc.constraint_name = kcu.constraint_name " +
                "JOIN " +
                "    information_schema.columns AS c " +
                "ON " +
                "    c.table_name = tc.table_name AND c.column_name = kcu.column_name " +
                "LEFT JOIN " +
                "    information_schema.referential_constraints AS rc " +
                "ON " +
                "    tc.constraint_name = rc.constraint_name " +
                "WHERE " +
                "    tc.table_name = ? AND " +
                "    tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE') " +
                "ORDER BY " +
                "    tc.table_name, c.column_name;";

        String columnType = "";
        String columnName = "";

        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, tableName);
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    columnType = rs.getString("CONSTRAINT_TYPE");
                    columnName = columnName + "|" + rs.getString("COLUMN_NAME");
                    constraints.put(columnType, columnName);
                }
            }
        }
        return constraints;
    }

    /**
     * 构建唯一性索引的键，确保当索引涉及多个列时，键是唯一的。
     *
     * @param constraints 当前的约束映射
     * @param indexName 索引名
     * @param columnName 列名
     * @return 唯一的键
     */
    private static String buildIndexKey(Map<String, String> constraints, String indexName, String columnName) {
        List<String> columns = new ArrayList<>();
        String keyPrefix = indexName + ":";
        for (String key : constraints.keySet()) {
            if (key.startsWith(keyPrefix)) {
                columns.add(key.substring(keyPrefix.length()));
            }
        }
        columns.add(columnName);
        return indexName + ":" + String.join(",", columns);
    }

    /**
     * 获取共有的表
     * @param mysqlMetaData
     * @param oracleMetaData
     * @return
     * @throws SQLException
     */
    private static Set<String> getCommonTable(DatabaseMetaData mysqlMetaData, DatabaseMetaData oracleMetaData) throws SQLException {
        // 1.获取oracle数据库XX角色下所有表信息
        ResultSet tables = oracleMetaData.getTables(null, secondaryUser.toUpperCase(Locale.ROOT), "%", new String[]{"TABLE"});
        ArrayList<String> oracleTableNameList = new ArrayList<>();

        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            oracleTableNameList.add(tableName);
        }

        // 2.获取mysql数据库所有表信息
        ResultSet rs = mysqlMetaData.getTables(null, null, "%", new String[]{"TABLE"});

        ArrayList<String> mysqlTableNameList = new ArrayList<>();

        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");
            mysqlTableNameList.add(tableName);
        }
        // 找出两库相同表
        Set<String> commonTable = mysqlTableNameList.stream()
                .filter(oracleTableNameList::contains)
                .collect(Collectors.toSet());

        // 找出Mysql中独有的表
        Set<String> uniqueToMysqlTable = mysqlTableNameList.stream()
                .filter(e -> !oracleTableNameList.contains(e))
                .collect(Collectors.toSet());

        // 找出Oracle中独有的表
        Set<String> uniqueToOracleTable = oracleTableNameList.stream()
                .filter(e -> !mysqlTableNameList.contains(e))
                .collect(Collectors.toSet());

        System.out.println("共有表: " + commonTable);
        System.out.println("Mysql独有表: " + uniqueToMysqlTable);
        System.out.println("Oracle独有表: " + uniqueToOracleTable);
        return commonTable;
    }


    /**
     * 获取数据库连接
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static HashMap<String, Connection> getConnection() throws ClassNotFoundException, SQLException {
        // 读取YAML配置文件
        Yaml yaml = new Yaml();
        InputStream inputStream = StartupTask.class.getResourceAsStream("/application.yml");

        Map<String, Object> configMap = yaml.load(inputStream);
        Map<String, Object> dataSourceConfig = (Map<String, Object>) configMap.get("spring");
        Map<String, Object> primaryConfig = (Map<String, Object>) ((Map<String, Object>) dataSourceConfig.get("datasource")).get("mysql");
        Map<String, Object> secondaryConfig = (Map<String, Object>) ((Map<String, Object>) dataSourceConfig.get("datasource")).get("oracle");

        // 读取mysql数据库配置
        String primaryDriver = (String) primaryConfig.get("driver-class-name");
        String primaryUrl = (String) primaryConfig.get("jdbc-url");
        String primaryUser = (String) primaryConfig.get("username");
        String primaryPassword = (String) primaryConfig.get("password");

        // 加载mysql数据库驱动
        Class.forName(primaryDriver);

        // 获取mysql数据库连接
        Connection mysql = DriverManager.getConnection(primaryUrl, primaryUser, primaryPassword);

        // 读取oracle数据库配置
        String secondaryDriver = (String) secondaryConfig.get("driver-class-name");
        String secondaryUrl = (String) secondaryConfig.get("jdbc-url");
        secondaryUser = (String) secondaryConfig.get("username");
        String secondaryPassword = (String) secondaryConfig.get("password");

        // 加载oracle数据库驱动
        Class.forName(secondaryDriver);

        // 获取oracle数据库连接
        Connection oracle = DriverManager.getConnection(secondaryUrl, secondaryUser, secondaryPassword);

        HashMap<String, Connection> connectionMap = new HashMap<>();
        connectionMap.put("mysqlConnection", mysql);
        connectionMap.put("oracleConnection", oracle);
        return connectionMap;
    }

    /**
     * 获取表结构
     *
     * @param connection
     * @return
     * @throws SQLException
     */
    public static Map<String, LinkedHashMap<String, String>> getTableStructures(Connection connection) throws SQLException {
        Map<String, LinkedHashMap<String, String>> tableStructures = new LinkedHashMap<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getColumns(null, null, "%", null);

        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");
            String columnName = rs.getString("COLUMN_NAME");
            String columnType = rs.getString("TYPE_NAME");

            tableStructures.computeIfAbsent(tableName, k -> new LinkedHashMap<>()).put(columnName, columnType);
        }

        return tableStructures;
    }

    /**
     * 比较字段类型是否一致
     * @param mysqlConn
     * @param oracleConn
     * @param tableName
     * @throws SQLException
     */
    private static void compareColumnTypes(Connection mysqlConn, Connection oracleConn, String tableName) throws SQLException {
        HashMap<String, String> mysqlColumns = getMysqlColumnsMap(mysqlConn, tableName);
        HashMap<String, String> oracleColumns = getOracleColumnsMap(oracleConn, tableName);

        mysqlColumns.forEach((key, value) -> {
            System.out.println("当前对比字段是：" + key + " 对比结果：" + DatabaseTypeComparator.isTypeMatch(value, oracleColumns.get(key)) + " mysql列类型是：" + value + " " + "oracle列类型是：" + oracleColumns.get(key));
//            System.out.println("Column type match for column is : " + DatabaseTypeComparator.isTypeMatch(value, oracleColumns.get(key)) + " " + key + " " + value + " " + oracleColumns.get(key));
            if (!DatabaseTypeComparator.isTypeMatch(value, oracleColumns.get(key))) {
                // 字段类型不同的在这里处理
                System.out.println("该字段类型不匹配: " + key + " " + value + " " + oracleColumns.get(key));
            }
        });
    }


    /**
     * 比较数据条数是否一致
     * @param mysqlConn
     * @param oracleConn
     * @param tableName
     */
    private static Long compareRowCount(Connection mysqlConn, Connection oracleConn, String tableName) throws SQLException {
        long mysqlRowCount = getRowCount(mysqlConn, tableName, "MySQL");
        long oracleRowCount = getRowCount(oracleConn, tableName, "Oracle");

        if (mysqlRowCount == oracleRowCount) {
            System.out.println("两表行数匹配 " + tableName + ": " + mysqlRowCount);
        } else {
            System.out.println("两表行数不匹配 " + tableName + ": MySQL 总条数 " + mysqlRowCount + ", Oracle 总条数 " + oracleRowCount);
        }
        return Math.max(mysqlRowCount, oracleRowCount);
    }


    /**
     * 比较两表数据一致性
     * @param mysqlConnection
     * @param oracleConnection
     * @param tableName
     * @param totalRows
     * @return
     * @throws SQLException
     */
    public static Map<String, String[]> compareDataConsistency(Connection mysqlConnection, Connection oracleConnection, String tableName, Long totalRows) throws SQLException {
        // 存储mysql和oracle中不一致的数据
        Map<String, String[]> mysqlInconsistencies = new HashMap<>();
        Map<String, String[]> oracleInconsistencies = new HashMap<>();

        // 使用Map来存储每条记录的第一列和其对应的所有列，以备后续比较
        Map<String, String[]> mysqlData = new HashMap<>();
        Map<String, String[]> oracleData = new HashMap<>();

        // 分页读取数据
        for (int offset = 0; offset < totalRows; offset += PAGE_SIZE) {
            // 从MySQL读取数据
//            System.out.println("----------------------mysql--------------------------");
            ResultSet mysqlResultSet = readMysqlData(mysqlConnection, tableName, offset);
            while (mysqlResultSet.next()) {
                String key = mysqlResultSet.getString(1); // 假设第一列是字符串类型
                String[] values = new String[mysqlResultSet.getMetaData().getColumnCount()];
                for (int i = 1; i <= values.length; i++) {
                    values[i - 1] = mysqlResultSet.getString(i);
                }

//                System.out.println(Arrays.stream(values).collect(Collectors.joining(", ")));
                mysqlData.put(key, values);
            }

//            System.out.println("----------------------oracle--------------------------");
            // 从Oracle读取数据
            ResultSet oracleResultSet = readOracleData(oracleConnection, tableName, offset);
            while (oracleResultSet.next()) {
                String key = oracleResultSet.getString(1);
                // 减1是因为oracle11g的分页会多出一列，在这里排除这列数据
                String[] values = new String[oracleResultSet.getMetaData().getColumnCount() - 1];
                for (int i = 1; i <= values.length; i++) {
                    values[i - 1] = oracleResultSet.getString(i);
                }
//                System.out.println(Arrays.stream(values).collect(Collectors.joining(", ")));
                oracleData.put(key, values);
            }

            // 检查不一致的数据
            checkInconsistencies(mysqlData, oracleData, mysqlInconsistencies, oracleInconsistencies);
            // 对比完后释放分页内容，释放内存
            mysqlData.clear();
            oracleData.clear();
            System.out.println("offset：" + offset + "本页结束mysql不一致的数据有： " + mysqlInconsistencies);
            System.out.println("offset：" + offset + "本页结束oracle不一致的数据有： " + oracleInconsistencies);
        }

        return mysqlInconsistencies;
    }

    /**
     * 分页读取mysql数据
     * @param connection
     * @param tableName
     * @param offset
     * @return
     */
    private static ResultSet readMysqlData(Connection connection, String tableName, int offset) {
        try {
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM " + tableName + " LIMIT ?, ?");
            statement.setInt(1, offset);
            statement.setInt(2, PAGE_SIZE);
            return statement.executeQuery();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 分页读取oracle数据
     * @param connection
     * @param tableName
     * @param offset
     * @return
     * @throws SQLException
     */
    private static ResultSet readOracleData(Connection connection, String tableName, int offset) throws SQLException {
        try {
            System.out.println("Offset: " + offset + ", Page Size: " + PAGE_SIZE);
            // Oracle分页查询
            String quoteChar = getQuoteCharacter(connection, "Oracle");
            String sql = "select * from "
                    +"(select t.*,rownum num from "
                    + quoteChar + tableName + quoteChar
                    + "t where rownum<=? ) "
                    +"where num>?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, offset + PAGE_SIZE);
            statement.setInt(2, offset);

            return statement.executeQuery();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void checkInconsistenciesForDate(Map<String, String[]> mysqlData, Map<String, String[]> oracleData,
                                             Map<String, String[]> mysqlInconsistencies, Map<String, String[]> oracleInconsistencies) {
        // 因为分页数据不对齐，可能会导致后续比较结果不准确，所以需要先将这些数据添加回数据集中继续对比
        for (Map.Entry<String, String[]> entry : mysqlInconsistencies.entrySet()) {
            mysqlData.putIfAbsent(entry.getKey(), entry.getValue());
        }
        for (Map.Entry<String, String[]> entry : oracleInconsistencies.entrySet()) {
            oracleData.putIfAbsent(entry.getKey(), entry.getValue());
        }


        // 创建一个新的HashMap用于存储最终的不一致数据
        Map<String, String[]> mysqlFinalInconsistencies = new HashMap<>(mysqlInconsistencies);
        Map<String, String[]> oracleFinalInconsistencies = new HashMap<>(oracleInconsistencies);

        // 比较值是否一致
        for (Map.Entry<String, String[]> entry : mysqlData.entrySet()) {
            String key = entry.getKey();
            String[] mysqlValue = entry.getValue();
            String[] oracleValue = oracleData.get(key);

            // 检查值是否一致
            boolean consistent = true;
            if (oracleValue != null && mysqlValue.length == oracleValue.length) {
                for (int i = 0; i < mysqlValue.length; i++) {
                    if (isMysqlDateTimeField(mysqlValue[i]) && isOracleDateTimeField(oracleValue[i])) {
                        LocalDateTime mysqlDateTime = LocalDateTime.parse(mysqlValue[i], MYSQL_DATE_TIME_FORMATTER);
                        LocalDateTime oracleDateTime = LocalDateTime.parse(oracleValue[i], ORACLE_DATE_TIME_FORMATTER);
                        System.out.println("该字段是日期类型： " + mysqlDateTime + " oracleDateTime " + oracleDateTime);
                        if (!mysqlDateTime.equals(oracleDateTime)) {
                            consistent = false;
                            break;
                        }
                        // 防止null值
                    } else if (!Objects.equals(mysqlValue[i], oracleValue[i])) {
                        consistent = false;
                        break;
                    }
                }
            } else {
                consistent = false;
            }

            if (!consistent) {
                mysqlFinalInconsistencies.put(key, mysqlValue);
                System.out.println("MySQL Value: " + Arrays.toString(mysqlValue));
                System.out.println("Oracle Value: " + Arrays.toString(oracleValue));
                System.out.println("以上判断不一致: ");
            }

            else if (consistent) {
                mysqlFinalInconsistencies.put(key, mysqlValue);
                System.out.println("MySQL Value: " + Arrays.toString(mysqlValue));
                System.out.println("Oracle Value: " + Arrays.toString(oracleValue));
                System.out.println("以上判断一致: ");
            }
        }

        // 不需要再次遍历oracleData，因为上面的循环已经涵盖了所有情况
        // 更新inconsistencies引用，指向新的不一致数据集合
        mysqlInconsistencies.clear();
        mysqlInconsistencies.putAll(mysqlFinalInconsistencies);
        oracleInconsistencies.clear();
        oracleInconsistencies.putAll(oracleFinalInconsistencies);
    }

    /**
     * 对比分页数据是否一致
     * @param mysqlData
     * @param oracleData
     */
    public static void checkInconsistencies(Map<String, String[]> mysqlData, Map<String, String[]> oracleData,
                                             Map<String, String[]> mysqlInconsistencies, Map<String, String[]> oracleInconsistencies) {
//        System.out.println("合并前的mysql结果集：" + mysqlData);
//        System.out.println("合并前的oracle结果集：" + oracleData);
        // 因为分页数据不对齐，可能会导致后续比较结果不准确，所以需要先将这些数据添加回数据集中继续对比，去重
        for (Map.Entry<String, String[]> entry : mysqlInconsistencies.entrySet()) {
            mysqlData.putIfAbsent(entry.getKey(), entry.getValue());
        }
        for (Map.Entry<String, String[]> entry : oracleInconsistencies.entrySet()) {
            oracleData.putIfAbsent(entry.getKey(), entry.getValue());
        }

        // 创建一个新的HashMap用于存储最终的不一致数据
        Map<String, String[]> mysqlFinalInconsistencies = new HashMap<>();
        Map<String, String[]> oracleFinalInconsistencies = new HashMap<>();

        // 检查MySQL中有而Oracle中没有的数据
        for (Map.Entry<String, String[]> entry : mysqlData.entrySet()) {
            if (entry == null) {
                break;
            }
            String key = entry.getKey();
            String[] mysqlValue = entry.getValue();
            // 通过Key值获取对应的oracle数据，oracleValue为null代表数不存于oracle，这样就无需在代码中遍历oracle表
            String[] oracleValue = oracleData.get(key);

            boolean consistent = true;
            if (oracleValue != null && mysqlValue.length == oracleValue.length) {
                // 检查每个字段是否一致，仅比较前 MAX_MATCH_LENGTH 个字符
                for (int i = 0; i < mysqlValue.length; i++) {
                    // 日期类型特殊处理
                    if (isMysqlDateTimeField(mysqlValue[i]) && isOracleDateTimeField(oracleValue[i])) {
                        LocalDateTime mysqlDateTime = LocalDateTime.parse(mysqlValue[i], MYSQL_DATE_TIME_FORMATTER);
                        LocalDateTime oracleDateTime = LocalDateTime.parse(oracleValue[i], ORACLE_DATE_TIME_FORMATTER);
//                        System.out.println("该字段是日期类型： " + mysqlDateTime + " oracleDateTime " + oracleDateTime);
                        if (!mysqlDateTime.equals(oracleDateTime)) {
                            consistent = false;
                            break;
                        }
                        // 防止null值
                    } else {
                        String mysqlStr = mysqlValue[i] != null ? mysqlValue[i].substring(0, Math.min(mysqlValue[i].length(), MAX_MATCH_LENGTH)) : "";
                        String oracleStr = oracleValue[i] != null ? oracleValue[i].substring(0, Math.min(oracleValue[i].length(), MAX_MATCH_LENGTH)) : "";
                        if (!Objects.equals(mysqlStr, oracleStr)) {
                            consistent = false;
                            break;
                        }
                    }

                }
            // 其他情况认为不一致
            } else {
                consistent = false;
            }

            if (!consistent) {
                // 说明当前比较的mysqlValue和oracleValue不一致
                mysqlFinalInconsistencies.put(key, mysqlValue);
            }
        }

        // 检查Oracle中有而MySQL中没有的数据
        for (Map.Entry<String, String[]> entry : oracleData.entrySet()) {
            if (entry == null) {
                break;
            }
            String key = entry.getKey();
            String[] oracleValue = entry.getValue();
            String[] mysqlValue = mysqlData.get(key);
            boolean consistent = true;

            if (mysqlValue != null && oracleValue.length == mysqlValue.length) {
                // 检查每个字段是否一致，仅比较前 MAX_MATCH_LENGTH
                for (int i = 0; i < oracleValue.length; i++) {

                    // 日期类型特殊处理
                    if (isMysqlDateTimeField(mysqlValue[i]) && isOracleDateTimeField(oracleValue[i])) {
                        LocalDateTime mysqlDateTime = LocalDateTime.parse(mysqlValue[i], MYSQL_DATE_TIME_FORMATTER);
                        LocalDateTime oracleDateTime = LocalDateTime.parse(oracleValue[i], ORACLE_DATE_TIME_FORMATTER);
//                        System.out.println("该字段是日期类型： " + mysqlDateTime + " oracleDateTime " + oracleDateTime);
                        if (!mysqlDateTime.equals(oracleDateTime)) {
                            consistent = false;
                            break;
                        }
                        // 防止null值
                    } else {
                        String mysqlStr = mysqlValue[i] != null ? mysqlValue[i].substring(0, Math.min(mysqlValue[i].length(), MAX_MATCH_LENGTH)) : "";
                        String oracleStr = oracleValue[i] != null ? oracleValue[i].substring(0, Math.min(oracleValue[i].length(), MAX_MATCH_LENGTH)) : "";
                        if (!Objects.equals(mysqlStr, oracleStr)) {
                            consistent = false;
                            break;
                        }
                    }
                }
            }
            // 其他情况认为不一致
            else {
                consistent = false;
            }
            if (!consistent) {
                oracleFinalInconsistencies.put(key, oracleValue);
            }
        }

        // 更新inconsistencies引用，指向新的不一致数据集合
        mysqlInconsistencies.clear();
        mysqlInconsistencies.putAll(mysqlFinalInconsistencies);
        oracleInconsistencies.clear();
        oracleInconsistencies.putAll(oracleFinalInconsistencies);
//        System.out.println("对比结束mysql最终结果集：" + mysqlInconsistencies);
//        System.out.println("对比结束oracle最终结果集：" + oracleInconsistencies);
    }

    /**
     * 判断是否是mysql的日期类型
     * @param value
     * @return
     */
    private static boolean isMysqlDateTimeField(String value) {
        try {
            LocalDateTime.parse(value, MYSQL_DATE_TIME_FORMATTER);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 判断是否是oracle的日期类型
     * @param value
     * @return
     */
    private static boolean isOracleDateTimeField(String value) {
        try {
            LocalDateTime.parse(value, ORACLE_DATE_TIME_FORMATTER);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 获取数据总条数
     * @param conn
     * @param tableName
     * @param dbType
     * @return
     * @throws SQLException
     */
    private static long getRowCount(Connection conn, String tableName, String dbType) throws SQLException {
        String quoteChar = getQuoteCharacter(conn, dbType);
        String query = "SELECT COUNT(*) FROM " + quoteChar + tableName + quoteChar;

        try (PreparedStatement stmt = conn.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    /**
     * 获取Oracle数据库引号类型
     * @param conn
     * @param dbType
     * @return
     * @throws SQLException
     */
    private static String getQuoteCharacter(Connection conn, String dbType) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        String identifierQuoteString = metaData.getIdentifierQuoteString();
        if ("Oracle".equals(dbType) && identifierQuoteString.isEmpty()) {
            // Oracle does not always return a quote character, so we use double quotes
            return "\"";
        }
        return identifierQuoteString;
    }

    /**
     * 获取Mysql字段名和字段类型
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    private static HashMap<String, String> getMysqlColumnsMap(Connection conn, String tableName) throws SQLException {
        HashMap<String, String> columnMap = new HashMap<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        while (rs.next()) {
            columnMap.put(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
        }
//        System.out.println(columnMap);
        rs.close();
        return columnMap;
    }

    /**
     * 获取Oracle字段名和字段类型
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    private static HashMap<String, String> getOracleColumnsMap(Connection conn, String tableName) throws SQLException {
        HashMap<String, String> columnMap = new HashMap<>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '" + tableName + "'");
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String dataType = rs.getString("DATA_TYPE");
                int dataLength = rs.getInt("DATA_LENGTH");
                columnMap.put(columnName, dataType);
            }
//            System.out.println("oracle: " + columnMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return columnMap;
    }
}
